

<!DOCTYPE html>
<html lang="zh-CN" color-mode=light>
<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
  <meta http-equiv="X-UA-Compatible" content="ie=edge">
  <title>SQLserver - LrTry</title>
  <meta name="apple-mobile-web-app-capable" content="yes" />
  <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
  <meta name="google" content="notranslate" />
  
  <meta name="description" content="数据库设计与应用实训数据库设计题
以“library”...">
  <meta name="author" content="John Doe">
  <link rel="icon" href="/images/icons/favicon-16x16.png" type="image/png" sizes="16x16">
  <link rel="icon" href="/images/icons/favicon-32x32.png" type="image/png" sizes="32x32">
  <link rel="apple-touch-icon" href="/images/icons/apple-touch-icon.png" sizes="180x180">
  <meta rel="mask-icon" href="/images/icons/stun-logo.svg" color="#333333">
  
    <meta rel="msapplication-TileImage" content="/images/icons/favicon-144x144.png">
    <meta rel="msapplication-TileColor" content="#000000">
  

  
<link rel="stylesheet" href="/css/style.css">


  
    
<link rel="stylesheet" href="https://at.alicdn.com/t/font_1445822_p6ry5n7lrr.css">

  

  
    
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/fancybox/3.5.7/jquery.fancybox.min.css">

  

  
    
      
        
        
<link rel="stylesheet" href="https://cdn.bootcss.com/highlight.js/9.18.1/styles/xcode.min.css" name="highlight-style" mode="light">

      
        
        
<link rel="stylesheet" href="https://cdn.bootcss.com/highlight.js/9.18.1/styles/solarized-dark.min.css" name="highlight-style" mode="dark">

      
  

  <script>
    var CONFIG = window.CONFIG || {};
    var ZHAOO = window.ZHAOO || {};
    CONFIG = {
      isHome: false,
      fancybox: true,
      pjax: false,
      loading: {
        gif: '/images/theme/loading.gif',
        lottie: ''
      },
      lazyload: {
        enable: true,
        only_post: 'false',
        loading: {
          gif: '/images/theme/loading.gif',
          lottie: ''
        }
      },
      donate: {
        enable: true,
        alipay: '/images/theme/aaa.jpg',
        wechat: '/images/theme/bbb.jpg'
      },
      galleries: {
        enable: true
      },
      fab: {
        enable: true,
        always_show: false
      },
      carrier: {
        enable: true
      },
      daovoice: {
        enable: false
      },
      preview: {
        background: {
          default: '',
          api: ''
        },
        motto: {
          default: '我在开了灯的床头下，想问问自己的心啊。',
          typing: true,
          api: 'https://v2.jinrishici.com/one.json',
          data_contents: '["data","content"]'
        },
      },
      qrcode: {
        enable: true,
        type: 'url',
        image: 'https://pic.izhaoo.com/weapp-code.jpg',
      },
      toc: {
        enable: true
      },
      scrollbar: {
        type: 'default'
      },
      notification: {
        enable: false,
        delay: 4500,
        list: '',
        page_white_list: '',
        page_black_list: ''
      },
      search: {
        enable: false,
        path: ''
      }
    }
  </script>

  

  

<meta name="generator" content="Hexo 5.4.2"></head>

<body class="lock-screen">
  <div class="loading" id="loading"></div>
  
    


  <nav class="navbar">
    <div class="left">
      
        <i class="iconfont iconhome j-navbar-back-home"></i>
      
      
        <i class="iconfont iconqrcode j-navbar-qrcode"></i>
      
      
        <i class="iconfont iconmoono" id="color-toggle" color-toggle="light"></i>
      
      
    </div>
    <div class="center">SQLserver</div>
    <div class="right">
      <i class="iconfont iconmenu j-navbar-menu"></i>
    </div>
    
      <div id="qrcode-navbar"></div>
    
  </nav>

  
  

<nav class="menu">
  <div class="menu-container">
    <div class="menu-close">
      <i class="iconfont iconbaseline-close-px"></i>
    </div>
    <ul class="menu-content"><li class="menu-item">
        <a href="/ " class="underline "> 首页</a>
      </li><li class="menu-item">
        <a href="/galleries/ " class="underline "> 摄影</a>
      </li><li class="menu-item">
        <a href="/archives/ " class="underline "> 归档</a>
      </li><li class="menu-item">
        <a href="/tags/ " class="underline "> 标签</a>
      </li><li class="menu-item">
        <a href="/categories/ " class="underline "> 分类</a>
      </li><li class="menu-item">
        <a href="/about/ " class="underline "> 关于</a>
      </li></ul>
    
      <div class="menu-copyright"><p>Powered by <a target="_blank" href="https://hexo.io">Hexo</a>  |  Theme - <a target="_blank" href="https://github.com/izhaoo/hexo-theme-zhaoo">zhaoo</a></p></div>
    
  </div>
</nav>
  <main id="main">
  <div class="article-wrap">
    <div class="row container">
      <div class="col-xl-3"></div>
      <div class="col-xl-6"><article class="article">
  <div class="wrap">
    <section class="head">
  <img   class="lazyload" data-original="/images/theme/post-image.jpg" src=""  draggable="false">
  <div class="head-mask">
    <h1 class="head-title">SQLserver</h1>
    <div class="head-info">
      <span class="post-info-item"><i class="iconfont iconcalendar"></i>三月 08, 2023</span>
      
      <span class="post-info-item"><i class="iconfont iconfont-size"></i>2040</span>
    </div>
  </div>
</section>
    <section class="main">
      <section class="content">
        
        <h1 id="数据库设计与应用实训"><a href="#数据库设计与应用实训" class="headerlink" title="数据库设计与应用实训"></a>数据库设计与应用实训</h1><h2 id="数据库设计题"><a href="#数据库设计题" class="headerlink" title="数据库设计题"></a>数据库设计题</h2><ol>
<li><p>以“library”为名称创建一个数据库。该数据库中包含一个主数据文件tsdata.mdf，存放路径为“d:\data\”；一个事务日志文件tslog.ldf，存放路径为“d:\data\”。其他设置自定。<br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/237daad081be4908baaa97e4de27c795.png" src="" ></p>
</li>
<li><p>在上题创建好的数据库中，按如下要求创建三张表。 </p>
</li>
</ol>
<p>表1 书籍表：用来存储书籍的基本信息  </p>
<table>
<thead>
<tr>
<th>字段名称</th>
<th>数据类型 长度 是否为空  说明</th>
</tr>
</thead>
<tbody><tr>
<td>序号</td>
<td>int - 非空-  初始值和增量均为1</td>
</tr>
<tr>
<td>图书编号</td>
<td>char-10  非空  主键</td>
</tr>
<tr>
<td>书名</td>
<td>varchar  50  非空</td>
</tr>
<tr>
<td>作者</td>
<td>varchar 20    非空</td>
</tr>
<tr>
<td>价格</td>
<td>Money  空</td>
</tr>
<tr>
<td>出版社</td>
<td>varchar  50    非空</td>
</tr>
<tr>
<td>出版日期</td>
<td>smalldatetime  空</td>
</tr>
<tr>
<td>库存量</td>
<td>int  非空  &gt;=0</td>
</tr>
</tbody></table>
<p><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/53b67bc60982422d8e4c69a738c6e6cc.png" src="" ><br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/4082cfc5e4a14a9098dc7f733d78ffba.png" src="" ><br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/735461d119084b9f82e73946ab2ee3b5.png" src="" ><br>3. 在“library”数据库中插入以下记录。<br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/4c4792b22f704b56839a19a899036843.png" src="" ><br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/d6173844a8cc4aa0b8e2ae392d7097c3.png" src="" ><br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/7fd8516d52a14a448f044c0705edb90c.png" src="" ><br>4. 、为读者表创建一个“姓名”列的非聚集索引文件。<br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/368fd005420c4ef19ae36866bc87e5a9.png" src="" ><br>5. 创建“读者借阅信息”视图，包括借书证号、姓名、书名、还书日期等信息。<br><img   class="lazyload" data-original="https://img-blog.csdnimg.cn/907fbbc4547c42a3b1db54bd2347e357.png" src="" ></p>
<h2 id="查询设计题"><a href="#查询设计题" class="headerlink" title="查询设计题"></a>查询设计题</h2><p>  请粘贴T-SQL查询语句： </p>
<ol>
<li>在library数据库中查询“孙一明”的相关信息。<br>select * from 读者表 where 姓名=’孙一明’;</li>
<li>查询信息系或电子系的读者信息。<br>select * from 读者表 where 单位=’信息系’ or 单位=’电子系’;</li>
<li>查找书名以“计算机”打头的所有图书和作者。<br>select * from 书籍表 where 书名 like ‘计算机%’;</li>
<li>查找姓名为“柯思扬”借阅书本的书名。<br>select 书名 from 书籍表 where 图书编号 in<br>(select 图书编号 from 借阅表 where 借书证号 =<br>(select 借书证号 from 读者表 where 姓名=’柯思扬’))</li>
<li>查询借书证号为“10001”所借书本的本数，显示借书证号和借书本数，并按借书证号升序排序。<br>select  b.借书证号,COUNT(*) 借书本数 from 借阅表 b,书籍表 a<br>where a.图书编号=b.图书编号 and b.借书证号=’10001’ group by b.借书证号 order by b.借书证号;</li>
</ol>
<h2 id="填空题"><a href="#填空题" class="headerlink" title="填空题"></a>填空题</h2><p>1、读者还书存储过程：ReturnBook的创建，若读者没有借阅此书，则显示‘对不起，你没有借阅此书，故而无法进行此次还书操作，请核实！’信息。<br>use Library</p>
<p>go</p>
<p>create  procedure <em><strong>ReturnBook</strong></em></p>
<p>@no char(10),@bid char(10)</p>
<p>as</p>
<p>if not exists(<em><strong>select * from 借阅表where 借书证号=@no and 图书编号=@bid</strong></em>___________________________________________________)</p>
<p>begin</p>
<pre><code>   print&#39;对不起，你没有借阅此书，故而无法进行此次还书操作，请核实！&#39;
</code></pre>
<p>end<br>2. 在借阅表中创建一个触发器：tri_Book，若要借的书已无库存，则无法进行借书操作，即无法在‘借阅表’中插入记录。<br>create <strong><strong>trigger</strong></strong>_ tri_Book ________</p>
<p>on <em><strong><strong>借阅表</strong></strong></em>__</p>
<p>for  insert</p>
<p>as</p>
<p>declare @btotal varchar(10),@bborrowed varchar(10)</p>
<p>select  @bborrowed=图书编号  from inserted</p>
<p>select @btotal=库存量 from 书籍表 where 图书编号=@bborrowed</p>
<p>if(<em><strong>@btotao=0</strong></em>________)</p>
<p>begin</p>
<p>rollback transaction</p>
<p>print ‘借阅失败！’</p>
<p>print’对不起，此书已经没有库存，无法进行本次借书操作！’</p>
<p>end</p>
<p>go  </p>
<h2 id="程序题（共15分）"><a href="#程序题（共15分）" class="headerlink" title="程序题（共15分）"></a>程序题（共15分）</h2><p>1、读者还书存储过程：ReturnBook_1的创建，1.成功还书时将归还否字段的‘否’改成‘是’，还书日期为当前时间，3.显示“成功地向图书馆归还！”。<br>create procedure ReturnBook_1</p>
<p>@no varchar(10),@bid varchar(30)</p>
<p>as</p>
<p>if exists(select *  from 借阅表 where 借书证号=@no and 图书编号=@bid and 归还否=’否’)</p>
<p>begin</p>
<p>update 借阅表 set 归还否=’是’,借书日期=GETDATE()</p>
<p>where 借书证号=@no and 图书编号=@bid</p>
<p>select ‘成功地向图书馆归还!’</p>
<p>end</p>
<p>go<br>2. 用借书证号和图书编号为“10001”和“j1” 来验证存储过程。<br>exec ReturnBook_1 ‘10001’,’J1’</p>
<p>select * from 借阅表;</p>

      </section>
      <section class="extra">
        
          <ul class="copyright">
  
    <li><strong>本文作者：</strong>John Doe</li>
    <li><strong>本文链接：</strong><a href="https://lrtry.gitee.io/2023/03/08/blog_02_sqlsever/index.html" title="https:&#x2F;&#x2F;lrtry.gitee.io&#x2F;2023&#x2F;03&#x2F;08&#x2F;blog_02_sqlsever&#x2F;index.html">https:&#x2F;&#x2F;lrtry.gitee.io&#x2F;2023&#x2F;03&#x2F;08&#x2F;blog_02_sqlsever&#x2F;index.html</a></li>
    <li><strong>版权声明：</strong>本博客所有文章均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh" title="BY-NC-SA" target="_blank" rel="noopener">BY-NC-SA</a> 许可协议，转载请注明出处！</li>
  
</ul>
        
        
          <section class="donate">
  <div id="qrcode-donate">
    <img   class="lazyload" data-original="/images/theme/aaa.jpg" src="" >
  </div>
  <div class="icon">
    <a href="javascript:;" id="alipay"><i class="iconfont iconalipay"></i></a>
    <a href="javascript:;" id="wechat"><i class="iconfont iconwechat-fill"></i></a>
  </div>
</section>
        
        
  <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag">数据库</a></li></ul> 

        
  <nav class="nav">
    <a></a>
    <a href="/2023/03/03/blog/">网站搭建<i class="iconfont iconright"></i></a>
  </nav>

      </section>
      
    </section>
  </div>
</article></div>
      <div class="col-xl-3">
        
          
  <aside class="toc-wrap">
    <h3 class="toc-title">文章目录：</h3>
    <ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1%E4%B8%8E%E5%BA%94%E7%94%A8%E5%AE%9E%E8%AE%AD"><span class="toc-text">数据库设计与应用实训</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1%E9%A2%98"><span class="toc-text">数据库设计题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E6%9F%A5%E8%AF%A2%E8%AE%BE%E8%AE%A1%E9%A2%98"><span class="toc-text">查询设计题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E5%A1%AB%E7%A9%BA%E9%A2%98"><span class="toc-text">填空题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E7%A8%8B%E5%BA%8F%E9%A2%98%EF%BC%88%E5%85%B115%E5%88%86%EF%BC%89"><span class="toc-text">程序题（共15分）</span></a></li></ol></li></ol>
  </aside>

        
      </div>
    </div>
  </div>
</main>
  

<footer class="footer">
  <div class="footer-social"><a 
        href="tencent://message/?Menu=yes&uin=894519210 "
        target="_blank"
        class="footer-social-item"
        onMouseOver="this.style.color= '#12B7F5'" 
        onMouseOut="this.style.color='#33333D'">
          <i class="iconfont  iconQQ "></i>
      </a><a 
        href="javascript:; "
        target="_blank"
        class="footer-social-item"
        onMouseOver="this.style.color= '#09BB07'" 
        onMouseOut="this.style.color='#33333D'">
          <i class="iconfont  iconwechat-fill "></i>
      </a><a 
        href="https://www.instagram.com/izhaoo/ "
        target="_blank"
        class="footer-social-item"
        onMouseOver="this.style.color= '#DA2E76'" 
        onMouseOut="this.style.color='#33333D'">
          <i class="iconfont  iconinstagram "></i>
      </a><a 
        href="https://github.com/zhaoo "
        target="_blank"
        class="footer-social-item"
        onMouseOver="this.style.color= '#9f7be1'" 
        onMouseOut="this.style.color='#33333D'">
          <i class="iconfont  icongithub-fill "></i>
      </a><a 
        href="mailto:izhaoo@163.com "
        target="_blank"
        class="footer-social-item"
        onMouseOver="this.style.color=#FF3B00" 
        onMouseOut="this.style.color='#33333D'">
          <i class="iconfont  iconmail"></i>
      </a></div>
  
    <div class="footer-copyright"><p>Powered by <a target="_blank" href="https://hexo.io">Hexo</a>  |  Theme - <a target="_blank" href="https://github.com/izhaoo/hexo-theme-zhaoo">zhaoo</a></p></div>
  
</footer>
  
      <div class="fab fab-plus">
    <i class="iconfont iconplus"></i>
  </div>
  
  
  
  <div class="fab fab-up">
    <i class="iconfont iconcaret-up"></i>
  </div>
  
  
  
    
<script src="/js/color-mode.js"></script>

  
  
</body>

<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>





  
<script src="https://cdn.bootcdn.net/ajax/libs/jquery.lazyload/1.9.1/jquery.lazyload.min.js"></script>




  
<script src="https://cdnjs.cloudflare.com/ajax/libs/fancybox/3.5.7/jquery.fancybox.min.js"></script>






  
<script src="https://cdn.bootcdn.net/ajax/libs/jquery.qrcode/1.0/jquery.qrcode.min.js"></script>




<script src="/js/utils.js"></script>
<script src="/js/script.js"></script>







  <script>
    (function () {
      var bp = document.createElement('script');
      var curProtocol = window.location.protocol.split(':')[0];
      if (curProtocol === 'https') {
        bp.src = 'https://zz.bdstatic.com/linksubmit/push.js';
      } else {
        bp.src = 'http://push.zhanzhang.baidu.com/push.js';
      }
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(bp, s);
    })();
  </script>













</html>